using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NCT.Framework;
using System.Data;
using System.Data.Common;
using NCT.Framework.Database;

namespace VISS.API.Users {
	class UserGeneralProfileDA {

		private const string SP_DecreaseArchivePoint = "dbo.UserGeneralProfile_DecreaseArchivePoint";
        private const string SP_DecreaseLeftPoint = "dbo.UserGeneralProfile_DecreaseleftPoint";
		private const string SP_DecreaseImageCount = "dbo.UserGeneralProfile_DecreaseImageCount";
		private const string SP_DecreaseTrusted = "dbo.UserGeneralProfile_DecreaseTrusted";
		private const string SP_GetAll = "dbo.UserGeneralProfile_GetAll";
		private const string SP_GetByUserKey = "dbo.UserGeneralProfile_GetByUserKey";
        private const string SP_GetImageCountByUserKey = "dbo.UserGeneralProfile_GetImageCountByUserKey";
        private const string SP_CountFemale = "dbo.UserGeneralProfile_CountFemale";
        private const string SP_CountMale = "dbo.UserGeneralProfile_CountMale";
        private const string SP_CountByAge = "dbo.UserGeneralProfile_CountByAge";
		private const string SP_IncreaseArchivePoint = "dbo.UserGeneralProfile_IncreaseArchivePoint";
		private const string SP_IncreaseImageCount = "dbo.UserGeneralProfile_IncreaseImageCount";
		private const string SP_IncreaseTrusted = "dbo.UserGeneralProfile_IncreaseTrusted";
		private const string SP_Insert = "dbo.UserGeneralProfile_Insert";
		private const string SP_Update = "dbo.UserGeneralProfile_Update";
        private const string SP_UpdateWithoutBlast = "dbo.UserGeneralProfile_UpdateWithoutBlast";
		private const string SP_UpdateBlast = "dbo.UserGeneralProfile_UpdateBlast";

		public static UserGeneralProfileEnt CreateFromReader(IDataReader reader) {
			UserGeneralProfileEnt item = new UserGeneralProfileEnt();
			item.UserKey = ConvertUtils.ToString(reader["UserKey"]);
			item.Name = ConvertUtils.ToString(reader["Name"]);
			item.Gender = (EUserGender)ConvertUtils.ToByte(reader["Gender"]);
			item.BirthDate = ConvertUtils.ToDateTime(reader["BirthDate"]);
			item.CityID = ConvertUtils.ToShort(reader["CityID"]);
			item.City = ConvertUtils.ToString(reader["City"]);
			item.CountryID = ConvertUtils.ToShort(reader["CountryID"]);
			item.Country = ConvertUtils.ToString(reader["Country"]);
			item.EducationID = ConvertUtils.ToShort(reader["EducationID"]);
			item.Education = ConvertUtils.ToString(reader["Education"]);
			item.JobID = ConvertUtils.ToShort(reader["JobID"]);
			item.Job = ConvertUtils.ToString(reader["Job"]);
			item.Hobby = ConvertUtils.ToString(reader["Hobby"]);
            item.LookingFor = (EUserLookingFor)ConvertUtils.ToByte(reader["LookingFor"]);
			item.ImageCount = ConvertUtils.ToInt(reader["ImageCount"]);
			item.ArchivePoint = ConvertUtils.ToInt(reader["ArchivePoint"]);
            item.LeftPoint = ConvertUtils.ToInt(reader["LeftPoint"]);
			item.Blast = ConvertUtils.ToString(reader["Blast"]);
			item.Trusted = ConvertUtils.ToInt(reader["Trusted"]);
			item.DateModified = ConvertUtils.ToDateTime(reader["DateModified"]);
			return item;
		}

        public static List<UserGeneralProfileEnt> GetAll(string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			List<UserGeneralProfileEnt> result = new List<UserGeneralProfileEnt>();
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetAll)) {
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					while (reader.Read()) {
						result.Add(CreateFromReader(reader));
					}
				}
			}
			return result;
		}

        public static UserGeneralProfileEnt GetByUserKey(string userKey, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			UserGeneralProfileEnt result = null;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetByUserKey)) {
				db.AddParameter(cmd, "UserKey", userKey);
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					if (reader.Read()) {
						result = CreateFromReader(reader);
					}
				}
			}
			return result;
		}

        public static int GetImageCountByUserKey(string userKey, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetImageCountByUserKey)) {
                db.AddParameter(cmd, "UserKey", userKey);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToInt(reader["ImageCount"]);
                    }
                }
            }
            return result;
        }

        

        public static long CountFemale(string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountFemale)) {
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static long CountMale(string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountMale)) {
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static long CountByAgeRange(DateTime fromBirthDate, DateTime toBirthDate, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountByAge)) {
                db.AddParameter(cmd, "FromBirthDate", fromBirthDate);
                db.AddParameter(cmd, "ToBirthDate", toBirthDate);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static int Insert(UserGeneralProfileEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_Insert)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "Name", item.Name);
				db.AddParameter(cmd, "Gender", ConvertUtils.ToBool((byte)item.Gender));
				db.AddParameter(cmd, "BirthDate", item.BirthDate);
				db.AddParameter(cmd, "CityID", item.CityID);
				db.AddParameter(cmd, "CountryID", item.CountryID);
				db.AddParameter(cmd, "EducationID", item.EducationID);
				db.AddParameter(cmd, "JobID", item.JobID);
				db.AddParameter(cmd, "Hobby", item.Hobby);
				db.AddParameter(cmd, "LookingFor", (byte)item.LookingFor);
				db.AddParameter(cmd, "ImageCount", item.ImageCount);
				db.AddParameter(cmd, "ArchivePoint", item.ArchivePoint);
                db.AddParameter(cmd, "LeftPoint", item.LeftPoint);
				db.AddParameter(cmd, "Blast", item.Blast);
				db.AddParameter(cmd, "Trusted", item.Trusted);
                db.AddParameter(cmd, "DateModified", item.DateModified);
				
                if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int Update(UserGeneralProfileEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_Update)) {
                db.AddParameter(cmd, "UserKey", item.UserKey);
                db.AddParameter(cmd, "Name", item.Name);
                db.AddParameter(cmd, "Gender", item.Gender);
                db.AddParameter(cmd, "BirthDate", item.BirthDate);
                db.AddParameter(cmd, "CityID", item.CityID);
                db.AddParameter(cmd, "CountryID", item.CountryID);
                db.AddParameter(cmd, "EducationID", item.EducationID);
                db.AddParameter(cmd, "JobID", item.JobID);
                db.AddParameter(cmd, "Hobby", item.Hobby);
                db.AddParameter(cmd, "LookingFor", item.LookingFor);
                db.AddParameter(cmd, "Blast", item.Blast);
                db.AddParameter(cmd, "DateModified", item.DateModified);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                }
            }
            return result;
        }

        public static int UpdateWithoutBlast(UserGeneralProfileEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateWithoutBlast)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "Name", item.Name);
				db.AddParameter(cmd, "Gender", item.Gender);
				db.AddParameter(cmd, "BirthDate", item.BirthDate);
                db.AddParameter(cmd, "CityID", item.CityID);
                db.AddParameter(cmd, "CountryID", item.CountryID);
				db.AddParameter(cmd, "EducationID", item.EducationID);
				db.AddParameter(cmd, "JobID", item.JobID);
				db.AddParameter(cmd, "Hobby", item.Hobby);
				db.AddParameter(cmd, "LookingFor", item.LookingFor);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int UpdateBlast(UserGeneralProfileEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateBlast)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "Blast", item.Blast);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int IncreaseArchivePoint(string userKey, int archivePointNCT, out int archivePoint, out int leftPoint, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_IncreaseArchivePoint)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "ArchivePointNCT", archivePointNCT);
                db.AddParameterOuter(cmd, "ArchivePoint", DbType.Int32);
                db.AddParameterOuter(cmd, "LeftPoint", DbType.Int32);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    archivePoint = (int)cmd.Parameters["ArchivePoint"].Value;
                    leftPoint = (int)cmd.Parameters["LeftPoint"].Value;
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                    archivePoint = (int)cmd.Parameters["ArchivePoint"].Value;
                    leftPoint = (int)cmd.Parameters["LeftPoint"].Value;
                }
            }
            return result;
        }

        public static int IncreaseImageCount(string userKey, int imageCountNCT, out int imageCount, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_IncreaseImageCount)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "ImageCountNCT", imageCountNCT);
                db.AddParameterOuter(cmd, "ImageCount", DbType.Int32);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    imageCount = ConvertUtils.ToInt(cmd.Parameters["ImageCount"].Value);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                    imageCount = ConvertUtils.ToInt(cmd.Parameters["ImageCount"].Value);
                }
            }
            return result;
        }

        public static int IncreaseTrusted(string userKey, int trustedNCT, out int trusted, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_IncreaseTrusted)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "TrustedNCT", trustedNCT);
                db.AddParameterOuter(cmd, "Trusted", DbType.Int32);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    trusted = (Int32)cmd.Parameters["Trusted"].Value;
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                    trusted = (Int32)cmd.Parameters["Trusted"].Value;
                }
            }
            return result;
        }

        public static int DecreaseArchivePoint(string userKey, int archivePointNCT, out int archivePoint, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_DecreaseArchivePoint)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "ArchivePointNCT", archivePointNCT);
                db.AddParameterOuter(cmd, "ArchivePoint", DbType.Int32);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    archivePoint = (Int32)cmd.Parameters["ArchivePoint"].Value;
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                    archivePoint = (Int32)cmd.Parameters["ArchivePoint"].Value;
                }
            }
            return result;
        }

        public static int DecreaseLeftPoint(string userKey, int leftPointNCT, out int leftPoint, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_DecreaseLeftPoint)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "LeftPointNCT", leftPointNCT);
                db.AddParameterOuter(cmd, "LeftPoint", DbType.Int32);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    leftPoint = (Int32)cmd.Parameters["LeftPoint"].Value;
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                    leftPoint = (Int32)cmd.Parameters["LeftPoint"].Value;
                }
            }
            return result;
        }

        public static int DecreaseImageCount(string userKey, int imageCountNCT, out int imageCount, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_DecreaseImageCount)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "ImageCountNCT", imageCountNCT);
                db.AddParameterOuter(cmd, "ImageCount", DbType.Int32);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    imageCount = (Int32)cmd.Parameters["ImageCount"].Value;
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                    imageCount = (Int32)cmd.Parameters["ImageCount"].Value;
                }
            }
            return result;
        }

        public static int DecreaseTrusted(string userKey, int trustedNCT, out int trusted, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_DecreaseTrusted)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "TrustedNCT", trustedNCT);
                db.AddParameterOuter(cmd, "Trusted", DbType.Int32);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    trusted = (Int32)cmd.Parameters["Trusted"].Value;
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                    trusted = (Int32)cmd.Parameters["Trusted"].Value;
                }
            }
            return result;
        }
	}
}